const { Pool } = require('pg');
require('dotenv').config({ path: '../backend/.env' });

// Database configuration with correct credentials
const pool = new Pool({
  user: process.env.DB_USER,
  host: process.env.DB_HOST,
  database: process.env.DB_NAME,
  password: process.env.DB_PASSWORD,
  port: process.env.DB_PORT
});

async function verifyDatabase() {
  let client;
  try {
    // Connect to the database
    client = await pool.connect();
    console.log('Connected to the database successfully');

    // Check if tables exist
    const tables = ['users', 'categories', 'bills', 'budgets', 'reports', 'ai_suggestions'];
    for (const table of tables) {
      const result = await client.query(`
        SELECT EXISTS (
          SELECT FROM information_schema.tables 
          WHERE table_name = $1
        )`, [table]);
      
      if (result.rows[0].exists) {
        console.log(`✓ Table "${table}" exists`);
      } else {
        console.log(`✗ Table "${table}" does not exist`);
      }
    }

    // Check default categories
    const categoriesResult = await client.query(`
      SELECT id, name, type, is_default 
      FROM categories 
      WHERE user_id = 0 AND is_default = TRUE
      ORDER BY id`);
    
    console.log(`\nFound ${categoriesResult.rows.length} default categories:`);
    categoriesResult.rows.forEach(category => {
      console.log(`  - ${category.name} (${category.type})`);
    });

    // Check indexes
    const indexesResult = await client.query(`
      SELECT indexname 
      FROM pg_indexes 
      WHERE tablename IN ('bills', 'categories', 'budgets', 'reports')
      AND indexname LIKE 'idx_%'`);
    
    console.log(`\nFound ${indexesResult.rows.length} indexes:`);
    indexesResult.rows.forEach(index => {
      console.log(`  - ${index.indexname}`);
    });

    client.release();
    await pool.end();
    console.log('\nDatabase verification completed successfully');
  } catch (error) {
    console.error('Error verifying database:', error.message);
    if (client) {
      client.release();
    }
    await pool.end();
  }
}

// Run the verification
verifyDatabase();